﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Public;

namespace DAL
{
    public class NhapKho_DAL
    {
        Connection conn = new Connection();
        NhapKho_PB nk_pb=new NhapKho_PB();

        #region "Load cbo đối tác"
        public DataTable LayDSNCC()
        {
            return conn.LoadData("select MANCC,TENNCC from NHACUNGCAP");
        }
        #endregion

        #region "Lấy tên ncc"
        public DataTable LayTenNCC(string mancc)
        {
            return conn.LoadData("select TENNCC from NHACUNGCAP where MANCC='"+mancc+"'");
        }
        #endregion

        #region "Thêm phiếu nhập kho"
        public int ThemNhapKho(NhapKho_PB nk_pb)
        {
            int nparameter = 7;
            string[] name = new string[nparameter];
            object[] value = new object[nparameter];
            name[0] = "@maphieunhap"; value[0] = NhapKho_PB.MaPhieuNhap;
            name[1] = "@mancc"; value[1] = NhapKho_PB.MaNCC;
            name[2] = "@nguoigiaohang"; value[2] = NhapKho_PB.NguoiGiaoHang;
            name[3] = "@diengiai"; value[3] = NhapKho_PB.DienGiai;
            name[4] = "@ngayhachtoan"; value[4] = NhapKho_PB.NgayHachToan;
            name[5] = "@ngaychungtu"; value[5] = NhapKho_PB.NgayChungTu;
            name[6] = "@sochungtu"; value[6] = NhapKho_PB.SoChungTu;
            string sql = "insert into PHIEUNHAP values (@maphieunhap,@mancc,@nguoigiaohang,@diengiai,@ngayhachtoan,@ngaychungtu,@sochungtu)";
            return conn.Update(sql, name, value, nparameter);
        }
        #endregion

        #region "Lấy DS Phiếu nhập kho"
        public DataTable LayDSPhieuNhap()
        {
            return conn.LoadData("select * from PHIEUNHAP");
        }
        #endregion

        #region "Lấy DS Tồn Kho"
        public DataTable LayDSTonKho()
        {
            return conn.LoadData("select tk.MAVATTU,TENVATTU,tk.SOLUONG AS SOLUONGTON from TONKHO tk,VATTU vt where tk.MAVATTU=vt.MAVATTU");
        }
        #endregion

        #region "Lấy DS Kho"
        public DataTable LayDSKho()
        {
            return conn.LoadData("select MAKHO,TENKHO from KHO");
        }
        #endregion

        #region "Lấy DS chi tiết nhập kho"
        public DataTable LayDSCTNhapKho()
        {
            return conn.LoadData("select ID,MAPHIEUNHAP,MAHANG,vt.TENVATTU,MAKHO,DONVITINH,ctn.SOLUONG,DONGIA,THANHTIEN,HANSUDUNG from CHITIETNHAP ctn,TONKHO tk,VATTU vt,DONVITINH dvt where ctn.MAHANG=tk.MAVATTU and vt.DONVITINH=dvt.MADONVITINH and tk.MAVATTU=vt.MAVATTU and MAPHIEUNHAP='XXXX'");
        }
        #endregion

        #region "Thêm CT Nhập Kho"
        public int ThemCTNhapKho(NhapKho_PB nk_pb)
        {
            int nparameter = 7;
            string[] name = new string[nparameter];
            object[] value = new object[nparameter];
            name[0] = "@id"; value[0] = NhapKho_PB.ID;
            name[1] = "@mapn"; value[1] = NhapKho_PB.MaPhieuNhap;
            name[2] = "@mahang"; value[2] = NhapKho_PB.MaHang;
            name[3] = "@makho"; value[3] = NhapKho_PB.MaKho;
            name[4] = "@soluong"; value[4] = NhapKho_PB.SoLuong;
            name[5] = "@thanhtien"; value[5] = NhapKho_PB.ThanhTien;
            name[6] = "@dongia"; value[6] = NhapKho_PB.DonGia;
            string sql = "insert into CHITIETNHAP values (@id,@mapn,@mahang,@makho,@soluong,@thanhtien,@dongia)";

            return conn.Update(sql, name, value, nparameter);
        }
        #endregion

        #region "Sửa CT Nhập kho"
        public int SuaCTNhapKho(NhapKho_PB nk_pb)
        {
            int nparameter = 5;
            string[] name = new string[nparameter];
            object[] value = new object[nparameter];
            name[0] = "@id"; value[0] = NhapKho_PB.ID;
            name[1] = "@mahang"; value[1] = NhapKho_PB.MaHang;
            name[2] = "@makho"; value[2] = NhapKho_PB.MaKho;
            name[3] = "@soluong"; value[3] = NhapKho_PB.SoLuong;
            name[4] = "@dongia"; value[4] = NhapKho_PB.DonGia;
            string sql = "update CHITIETNHAP set MAHANG=@mahang,MAKHO=@makho,SOLUONG=@soluong,DONGIA=@dongia where ID=@id";
            return conn.Update(sql, name, value, nparameter);
        }
        #endregion

        #region "Xóa CT Nhập kho"
        public int XoaCTNhapKho(NhapKho_PB nk_pb)
        {
            int nparameter = 1;
            string[] name = new string[nparameter];
            object[] value = new object[nparameter];
            name[0] = "@id"; value[0] = NhapKho_PB.ID;
            //name[1] = "@mapn"; value[1] = NhapKho_PB.MaPhieuNhap;
            //name[1] = "@mahang"; value[1] = NhapKho_PB.MaHang;
            //name[2] = "@makho"; value[2] = NhapKho_PB.MaKho;
            //name[3] = "@soluong"; value[3] = NhapKho_PB.SoLuong;
            //name[4] = "@dongia"; value[4] = NhapKho_PB.DonGia;
            string sql = "delete from CHITIETNHAP where ID=@id";
            return conn.Update(sql, name, value, nparameter);
        }
        #endregion

        #region "Tìm theo mã phiếu nhập"
        public DataTable TimMaPhieuNhap(string ma)
        {
            string sql = string.Format("select ID,MAHANG,vt.TENVATTU,MAKHO,ctn.SOLUONG,DONGIA,HANSUDUNG from CHITIETNHAP ctn,TONKHO tk,VATTU vt where ctn.MAHANG=tk.MAVATTU and tk.MAVATTU=vt.MAVATTU and MAPHIEUNHAP='{0}'",ma);
            return conn.LoadData(sql);
        }
        #endregion

        #region "Tăng mã tự động"
        public string NextID()
        {
            return NextID_PB.NextID(conn.GetLastID("CHITIETNHAP", "ID"), "CTN");
        }
        #endregion


    }
}
